package com.jnu.accounthelper.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build;

import androidx.annotation.RequiresApi;


import java.lang.reflect.Array;
import java.text.ParseException;
import java.time.LocalDate;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.List;

public class DBManager {
    private static SQLiteDatabase db;
    private static Cursor cursor;
    public static void initDB(Context context){
        DBHelper helper=new DBHelper(context);
        db=helper.getWritableDatabase();
    }
    //获取分类表中所有记录
    public static ArrayList<Type> getTypeList() throws ParseException {
        ArrayList<Type> list=new ArrayList<>();
        String sql="select * from account_types";
        cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String name=cursor.getString(cursor.getColumnIndex("name"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            Type type =new Type(name,imageId);
            list.add(type);
        }
        cursor.close();
        return list;
    }
    //获取账单表中所有对象
    public static ArrayList<Account> getAccountList() throws ParseException {
        ArrayList<Account> list=new ArrayList<>();
        String sql="select * from accounts";
        cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            int id=cursor.getInt(cursor.getColumnIndex("_id"));
            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            acc.setId(id);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
    public static ArrayList<Account> getAccountsByType(String s) throws ParseException {
        ArrayList<Account> list=new ArrayList<>();
        String sql="select * from accounts where type=\""+s+"\"";
        cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            //acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
    //显示总和
    public static double getSum(){
        String sql="select sum(number) from accounts";
        double sum=0;
        cursor=db.rawQuery(sql,null);
        if(cursor.moveToFirst()){
            sum=cursor.getInt(cursor.getColumnIndex("sum(number)"));
        }
        cursor.close();
        return sum;
    }
    //显示收入
    public static double getInput(){
        String sql="select sum(number) from accounts where number>0";
        double sum=0;
        cursor=db.rawQuery(sql,null);
        if(cursor.moveToFirst()){
            sum=cursor.getInt(cursor.getColumnIndex("sum(number)"));
        }
        cursor.close();
        return sum;
    }
    //显示支出
    public static double getOutput(){
        String sql="select sum(number) from accounts where number<0";
        double sum=0;
        cursor=db.rawQuery(sql,null);
        if(cursor.moveToFirst()){
            sum=cursor.getInt(cursor.getColumnIndex("sum(number)"));
        }
        cursor.close();
        return sum;
    }
    //显示分类总和
    @RequiresApi(api = Build.VERSION_CODES.N)
    public static ArrayList<Account> getTypeSum(int year , int month, int kind){
        ArrayList<Account> list=new ArrayList<>();
        ArrayList<Account> type_list=new ArrayList<>();
        String sql;
        String d;
        if(month<1){
            year--;
            month=12+month;
        }
        if(month<10){
            d=year+"年"+"0"+month+"月";
        }

        else{
            d=year+"年"+month+"月";
        }
        if(kind==1){
            sql="select * from accounts where number>0 and date like \""+d+"%"+"\"";
        }else {
            sql="select * from accounts where number<0 and date like \""+d+"%"+"\"";
        }

        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            int id=cursor.getInt(cursor.getColumnIndex("_id"));
            Account account=new Account(type,remark,number,imageId,source);
            account.setId(id);
            account.setDate(date);
            list.add(account);
        }
        cursor.close();

        return list;
    }
    //添加分类
    public static void insertToType(Type type){
        ContentValues values=new ContentValues();
        values.put("name",type.getName());
        values.put("CoverResourceId",type.getCoverResourceId());
        db.insert("account_types",null,values);
    }
    public ArrayList<Account> SelectByType(String t){
        ArrayList<Account> list=new ArrayList<>();
        String sql="select * from accounts where type="+t;
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            int id=cursor.getInt(cursor.getColumnIndex("_id"));
            Account account=new Account(type,remark,number,imageId,source);
            account.setId(id);
            account.setDate(date);

            list.add(account);
        }
        cursor.close();
        return list;
    }
    //更新账单记录
    public static void UpdateToAccount(String type,int number,String src,String remark){
        ContentValues values=new ContentValues();
        values.put("remark",remark);
        values.put("number",number);
        values.put("Source",src);
        db.update("accounts",values,"type=?",new String[]{type});
    }
    public static void deleteAccount(Account account){
        db.delete("accounts","_id=?",new String[]{String.valueOf(account.getId())});
    }
    //添加账单记录
    public static void insertToAccount(Account account){
        ContentValues values=new ContentValues();
        values.put("type",account.getType());
        values.put("remark",account.getRemark());
        values.put("number",account.getNumber());
        values.put("CoverResourceId",account.getCoverResourceId());
        values.put("Source",account.getSource());
        values.put("date",account.getDate());
        db.insert("accounts",null,values);
    }
    //根据日期查找记录
    public static  ArrayList<Account> SelectByDay(int year ,int month, int day){
        ArrayList<Account> list=new ArrayList<>();
        String d;
        if(month<10&&day<10){
            d=year+"年"+"0"+month+"月"+"0"+day+"日";
        }
        else if(month<10){
            d=year+"年"+"0"+month+"月"+day+"日";
        }
        else if(day<10){
            d=year+"年"+month+"月"+"0"+day+"日";
        }
        else{
            d=year+"年"+month+"月"+day+"日";
        }
        String sql="select * from accounts where date=\""+d+"\"";
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
    @RequiresApi(api = Build.VERSION_CODES.O)
    public static  ArrayList<Account> SelectByDay(int year , int month, int day, int kind){
        ArrayList<Account> list=new ArrayList<>();
        String d;
        if(day<1){
            LocalDate thismonth=LocalDate.of(year,month,day);
            LocalDate lastMonth = thismonth.minusMonths(1); // 当前月份减1
            LocalDate lastDay = lastMonth.with(TemporalAdjusters.lastDayOfMonth());
            day=lastDay.getDayOfMonth()+day;
            month--;
            if(month<1){
                month=12;
                year--;
            }
        }
            if(month<10&&day<10){
                d=year+"年"+"0"+month+"月"+"0"+day+"日";
            }
            else if(month<10){
                d=year+"年"+"0"+month+"月"+day+"日";
            }
            else if(day<10){
                d=year+"年"+month+"月"+"0"+day+"日";
            }
            else{
                d=year+"年"+month+"月"+day+"日";
            }

        String sql;
        if(kind==1){
            sql="select * from accounts where number>0 and date = \""+d+"\"";
        }
        else sql="select * from accounts where number<0 and date = \""+d+"\"";
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
    public static  ArrayList<Account> SelectByDay(String day,int kind){
        ArrayList<Account> list=new ArrayList<>();
        String sql;
        if(kind==1)
            sql= "select * from accounts where number>0 and date=\""+day+"\"";
        else if(kind==2){
            sql= "select * from accounts where number<0 and date=\""+day+"\"";
        }
        else sql= "select * from accounts where date=\""+day+"\"";
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
    //根据月份查找记录
    public static  ArrayList<Account> SelectByMonth(int year ,int month){
        ArrayList<Account> list=new ArrayList<>();
        String d;
        if(month<1){
            year--;
            month=12+month;
        }
        if(month<10){
            d=year+"年"+"0"+month+"月";
        }

        else{
            d=year+"年"+month+"月";
        }
        String sql="select * from accounts where date like "+d;
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
    public static  ArrayList<Account> SelectByMonth(int year ,int month,int kind){
        ArrayList<Account> list=new ArrayList<>();
        String d;
        if(month<1){
            year--;
            month=12+month;
        }
        if(month<10){
            d=year+"年"+"0"+month+"月";
        }
        else{
            d=year+"年"+month+"月";
        }
        String sql;
        if(kind==1){
            sql="select * from accounts where number>0 and date like \""+d+"%"+"\"";
        }
        else sql="select * from accounts where number<0 and date like \""+d+"%"+"\"";
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));
            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }

    //根据年份查找记录
    public static  ArrayList<Account> SelectByYear(int year,int kind ){
        ArrayList<Account> list=new ArrayList<>();
        String sql;
        String d=year+"年";
        if(kind==1){
            sql="select * from accounts where number>0 and date like \""+d+"\"";
        }else {
            sql="select * from accounts where number<0 and date like \""+d+"\"";
        }
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));

            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
    public static  ArrayList<Account> SelectFromTo(String start_day,String end_day,int kind ){
        ArrayList<Account> list=new ArrayList<>();
        String sql;
        if(kind==1){
            sql="select * from accounts where number>0 and date >= \""+start_day+"\""+"and date <=\""+end_day+"\"";
        }else {
            sql="select * from accounts where number<0 and date >= \""+start_day+"\""+"and date <=\""+end_day+"\"";
        }
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));

            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
    //根据备注查记录
    public static  ArrayList<Account> selectByRemark(String S_type,String S_remark){
        ArrayList<Account> list=new ArrayList<>();
        String sql;
        if(!S_type.equals(""))
            sql="select * from accounts where type=\""+S_type+"\""+" and remark like \""+S_remark+"%"+"\"";
        else sql="select * from accounts where remark like \""+S_remark+"%"+"\"";
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String remark=cursor.getString(cursor.getColumnIndex("remark"));
            int number=cursor.getInt(cursor.getColumnIndex("number"));
            int imageId=cursor.getInt(cursor.getColumnIndex("CoverResourceId"));
            String source = cursor.getString(cursor.getColumnIndex("source"));
            String date=cursor.getString(cursor.getColumnIndex("date"));

            Account acc =new Account(type,remark,number,imageId,source);
            acc.setDate(date);
            list.add(acc);
        }
        cursor.close();
        return list;
    }
}
